<?php
session_start(); 
	require('./include/phpsqlajax_dbinfo.php');
	
	function parseToKML($htmlStr){
		$kmlStr=str_replace('&','&amp;',$htmlStr);
		return $kmlStr; 
	}
	
	 // Opens a connection to a MySQL server.
	$connection = mysql_connect ($server, $username, $password);
	if (!$connection) 
	{
	  die('Not connected : ' . mysql_error());
	}
	
	// Sets the active MySQL database.
	$db_selected = mysql_select_db($database, $connection);
	if (!$db_selected) 
	{
	  die ('Can\'t use db : ' . mysql_error());
	}
	
	 //Set result charset to UTF-8
	 mysql_query('SET NAMES "utf8"');
	 
	 
	 
	 if(isset($_POST['city'])&& isset($_POST['eventRadius']) && $_POST['eventRadius'] != ''){
			$url='http://maps.googleapis.com/maps/api/geocode/xml?sensor=false&address='.urlencode($_POST['city']).'';
			$neuxml = simplexml_load_file($url) or die("url not loading");
			$status = $neuxml->status;
		  if ($status=="OK") {
			  $Lat = $neuxml->result->geometry->location->lat;
			  $Lon = $neuxml->result->geometry->location->lng;
			  $LatLng = "$Lat,$Lon";
		  }
		$query = 	'SELECT (6371*acos(cos(radians('.$Lat.'))*cos(radians(events.lat))*cos(radians(events.long) 
					- radians('.$Lon.'))+sin(radians('.$Lat.'))*sin(radians(events.lat)))) AS distance, 
					events.idEvents, events.lat, events.long, events.name, events.description,events.visibility,
					ROUND(events.fee,2) AS fee, events.date, events.town, events.plz, events.street,
					events.nr, eventcategories.eventcategory FROM events , eventcategories, events_has_eventcategories
					WHERE events_has_eventcategories.EventCategories_idEventCategories = eventcategories.idEventCategories
					AND events_has_eventcategories.Events_idEvents = events.idEvents ';	
	 	
		
		
	 	//Append filter parameters if they are set
		 if(isset($_POST['startdt']) && $_POST['startdt'] != ''){
		 	$query .= 'AND events.date >= "'.$_POST['startdt'].'" ';
		 }
		 if(isset($_POST['enddt']) && $_POST['enddt'] != ''){
		 	$query .= 'AND events.date <= "'.$_POST['enddt'].'" ';
		 }
		 if(isset($_POST['eventType']) && $_POST['eventType'] != ''){
		 	$query .= 'AND eventcategories.eventcategory = "'.$_POST['eventType'].'" ';	
		 }
		 if(isset($_POST['eventRadius']) && $_POST['eventRadius'] != ''){
		 	$query .= 'HAVING distance<"'.$_POST['eventRadius'].'" ';	
		 }	
		 
		 if(empty($_SESSION['privileges'])){
			$query .='AND events.visibility="1" ';
		 }
		
	 }else{
		$query = 	'SELECT events.idEvents, events.lat, events.long, events.name, events.description,events.visibility,
					ROUND(events.fee,2) AS fee, events.date, events.town, events.plz, events.street,
					events.nr, eventcategories.eventcategory FROM events , eventcategories, events_has_eventcategories
					WHERE events_has_eventcategories.EventCategories_idEventCategories = eventcategories.idEventCategories
					AND events_has_eventcategories.Events_idEvents = events.idEvents ';
	
		//Append filter parameters if they are set
		 if(isset($_POST['startdt']) && $_POST['startdt'] != ''){
		 	$query .= 'AND events.date >= "'.$_POST['startdt'].'" ';
		 }
		 if(isset($_POST['enddt']) && $_POST['enddt'] != ''){
		 	$query .= 'AND events.date <= "'.$_POST['enddt'].'" ';
		 }
		 if(isset($_POST['eventType']) && $_POST['eventType'] != ''){
		 	$query .= 'AND eventcategories.eventcategory = "'.$_POST['eventType'].'" ';
		 }	
		 if(empty($_SESSION['privileges'])){
			$query .='AND events.visibility="1" ';
		 }
	}
	 
	 $result = mysql_query($query);
	 if (!$result) 
	 {
	  die('Invalid query: ' . mysql_error());
	 }
	
	// Creates an array of strings to hold the lines of the XML file.
	header('Content-type: text/xml');
	$xml = array('<?xml version="1.0" encoding="UTF-8"?>');
	$xml[] = '<events>';
		
	// Iterates through the rows, printing a node for each row.
	while ($row = @mysql_fetch_assoc($result)) 
	{
	  $xml[] = '<event id="'.$row['idEvents'].'">';
	  $xml[] = '<name>'.$row['name'].'</name>';
	  $xml[] = '<description>'.$row['description'].'</description>';	
	  $xml[] = '<eventcategory>'.$row['eventcategory'].'</eventcategory>';	
	  $xml[] = '<fee>'.$row['fee'].'</fee>';				
	  $xml[] = '<date>'.$row['date'].'</date>';
	  $xml[] = '<address>';
	  $xml[] = '<street>'.$row['street'].'</street>';	
	  $xml[] = '<nr>'.$row['nr'].'</nr>';
	  $xml[] = '<plz>'.$row['plz'].'</plz>';
	  $xml[] = '<town>'.$row['town'].'</town>';			
	  $xml[] = '</address>';
	  $xml[] = '<location>';
	  $xml[] = '<lat>'.$row['lat'].'</lat>';			
	  $xml[] = '<long>'.$row['long'].'</long>';
	  $xml[] = '</location>';
	  $xml[] = '<visibility>'.$row['visibility'].'</visibility>';
	  $xml[] = '</event>';			
	} 
	
	// End XML file
	$xml[] = '</events>';
	$xmlOutput = join($xml);
	
	echo $xmlOutput;
?>